﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SubSonic.Query;
using SubSonic.Repository;
using System.Linq.Expressions;
using SubSonic.Schema;
using WebeTerm.Common;
using WebeTerm.Model;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;

namespace WebeTerm.DbProvider
{
    public abstract class BaseDAL<T> where T : class ,new()
    {
        //private DbQueryProvider provider;
        private IQuerySurface DataProvider = new SqlDbProvider(@"WebeTermContext");
        IRepository<T> e = null;
        protected BaseDAL()
        {
            e = new ClassicRepository<T>(DataProvider);
        }

        public IQueryable<T> All()
        {
            return e.GetAll();
        }

        public IQuerySurface Provider { get { return DataProvider; } }

        public T FirstOrDefault(Expression<Func<T, bool>> expression)
        {
            var item = Query(expression).FirstOrDefault();
            if (item is IModel) { (item as IModel).SetIsLoaded(); }
            return item;
        }

        /// <summary>
        /// 直接执行更新SQL
        /// </summary>
        /// <param name="Sql"></param>
        /// <returns></returns>
        public int ExecuteSql(string Sql, params object[] values)
        {
            try
            {
                InlineQuery query = new InlineQuery(Provider.Provider, Sql, values);
                return query.Execute();
            }
            catch (Exception ex) { throw ex; }
        }


        public List<V> ExecuteSql<V>(string Sql, params object[] values) where V :class,new(){
            try
            {
                InlineQuery query = new InlineQuery(Provider.Provider, Sql, values);
                return query.ExecuteTypedList<V>();
            }
            catch (Exception ex) { throw ex; }
        }


        public int ExecuteStoreProcedures(string CommandText, params DbParameter[] values)
        {
            lock (this)
            {
                DbCommand dbCommand = Provider.Provider.CreateCommand();
                dbCommand.CommandText = CommandText;
                dbCommand.CommandType = System.Data.CommandType.StoredProcedure;
                DbConnection dbConnection = Provider.Provider.CreateConnection();
                //dbConnection.Open();
                dbCommand.Connection = dbConnection;
                foreach (DbParameter p in values)
                    dbCommand.Parameters.Add(p);
                int val = dbCommand.ExecuteNonQuery();
                dbConnection.Dispose();
                return val;
            }
        }



        public DataSet ExecuteStoreProceduresDataSet(string CommandText, out int RecordCount, params DbParameter[] values)
        {
            lock (this)
            {
                RecordCount = 0;
                SqlCommand dbCommand = Provider.Provider.CreateCommand() as SqlCommand;
                foreach (DbParameter p in values)
                    dbCommand.Parameters.Add(p);
                dbCommand.CommandText = CommandText;
                dbCommand.CommandType = System.Data.CommandType.StoredProcedure;
                SqlConnection dbConnection = Provider.Provider.CreateConnection() as SqlConnection;
                dbCommand.Connection = dbConnection;
                dbCommand.CommandTimeout = 0;
                DataSet dataset = new DataSet();
                SqlDataAdapter dataadapter = new SqlDataAdapter(dbCommand);

                dataadapter.Fill(dataset);

                RecordCount = (int)dbCommand.Parameters[@"@RecordCount"].Value;

                dbConnection.Dispose();

                return dataset;
            }
        }

        public DataSet ExecuteStoreProceduresDataSet(string CommandText, out string RecordMsg, params DbParameter[] values)
        {
            lock (this)
            {
                RecordMsg = "";
                SqlCommand dbCommand = Provider.Provider.CreateCommand() as SqlCommand;
                foreach (DbParameter p in values)
                    dbCommand.Parameters.Add(p);
                dbCommand.CommandText = CommandText;
                dbCommand.CommandType = System.Data.CommandType.StoredProcedure;
                SqlConnection dbConnection = Provider.Provider.CreateConnection() as SqlConnection;
                dbCommand.Connection = dbConnection;
                DataSet dataset = new DataSet();
                SqlDataAdapter dataadapter = new SqlDataAdapter(dbCommand);

                dataadapter.Fill(dataset);

                RecordMsg = dbCommand.Parameters[@"@ReturnMsg"].Value as string;

                dbConnection.Dispose();

                return dataset;
            }
        }

        public DataTable ExecuteStoreProceduresDataTable(string CommandText, params DbParameter[] values)
        {
            lock (this)
            {
                DbCommand dbCommand = Provider.Provider.CreateCommand();
                dbCommand.CommandText = CommandText;
                dbCommand.CommandType = System.Data.CommandType.StoredProcedure;
                DbConnection dbConnection = Provider.Provider.CreateConnection();
                //dbConnection.Open();
                dbCommand.Connection = dbConnection;
                foreach (DbParameter p in values)
                    dbCommand.Parameters.Add(p);
                var reader = dbCommand.ExecuteReader();

                var table = new DataTable();
                table.Load(reader);
                reader.Close();
                dbConnection.Dispose();
                return table;
            }
        }



        /// <summary>
        /// 执行实体查询SQL
        /// </summary>
        /// <param name="Sql"></param>
        /// <returns></returns>
        public List<T> ExecuteTypeSql(string Sql)
        {
            try
            {
                InlineQuery query = new InlineQuery(Provider.Provider, Sql);
                return query.ExecuteTypedList<T>();
            }
            catch (Exception) { return new List<T>(); }
        }


        public PagedList<T> GetPaged<TKey>(Func<T, TKey> orderBy, Expression<Func<T, bool>> expression, int pageIndex, int pageSize)
        {
            var sql = DataProvider.GetQuery<T>().Where(expression).AsQueryable();
            int recordCount = sql.Count();
            var resultSql = sql.OrderBy(orderBy).Skip((pageIndex - 1) * pageSize).Take(pageSize);
            var items = resultSql.ToList();
            items.ForEach(l => { if (l is IModel) { (l as IModel).SetIsLoaded(); } });
            return new PagedList<T>(items, recordCount, pageIndex, pageSize);
            //return e.GetPaged<TKey>(orderBy, pageIndex, pageSize);
        }

        public PagedList<T> GetPaged(string sortBy, int pageIndex, int pageSize)
        {
            return e.GetPaged(sortBy, pageIndex, pageSize);
        }


        public IQueryable<T> Query(Expression<Func<T, bool>> expression)
        {
            return All().Where(expression);
        }



        public int DeleteMany(Expression<Func<T, bool>> expression)
        {
            return e.DeleteMany(expression);
        }


        public int Delete(T item)
        {
            return e.Delete(item);
        }


        public int Delete(IEnumerable<T> items)
        {
            if (items.Count() == 0) return 0;
            return e.Delete(items, DataProvider.Provider);
        }


        public int Update(T item)
        {
            return e.Update(item);
        }

        public int Update(IEnumerable<T> items)
        {
            if (items.Count() == 0) return 0;
            return e.Update(items, DataProvider.Provider);
        }


        public object Add(T item)
        {
            return e.Add(item, DataProvider.Provider);
        }


        public void Add(IEnumerable<T> items)
        {
            if (items.Count() == 0) return;
            e.Add(items, DataProvider.Provider);
        }



        /// <summary>
        /// 排序
        /// </summary>
        /// <typeparam name="T">查询对象类型</typeparam>
        /// <param name="queryable">需要排序的数据</param>
        /// <param name="sortField">排序字段</param>
        /// <returns>分页结果</returns>
        protected IQueryable<T> OrderBy<T>(IQueryable<T> queryable, KeyValuePair<string, bool> sortField)
        {
            string[] propertys = sortField.Key.Split('.');
            Type elementType = queryable.ElementType;

            // 参数
            ParameterExpression parameterExpression =
                Expression.Parameter(queryable.ElementType, "p");

            // 需要排序的属性
            MemberExpression memberExpression = null;

            for (int i = 0; i < propertys.Count(); i++)
            {
                string property = propertys[i];

                if (elementType.GetProperty(property) == null)
                {
                    //throw new ArgumentOutOfRangeException("sortFields", property, "排序字段不存在！/Sort field not exists!");
                    throw new CommonException("排序字段不存在!", 300003);
                }

                if (i == 0)
                {
                    // 第一个属性
                    memberExpression = Expression.Property(parameterExpression, property);
                }
                else
                {
                    // 非第一个属性
                    memberExpression = Expression.Property(memberExpression, property);
                }

                elementType = elementType.GetProperty(property).PropertyType;
            }

            Expression expression = Expression.Lambda(memberExpression, parameterExpression);

            // 调用Queryable的静态方法OrderBy进行排序
            MethodCallExpression methodCallExpression = Expression.Call(
                typeof(Queryable),
                sortField.Value ? "OrderBy" : "OrderByDescending",
                new Type[] { queryable.ElementType, elementType },
                queryable.Expression,
                expression);

            // 返回当页的数据
            var result = queryable.Provider.CreateQuery<T>(methodCallExpression);

            return result;
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <typeparam name="T">查询对象类型</typeparam>
        /// <param name="queryable">需要分页的数据</param>
        /// <param name="pages">分页信息</param>
        /// <returns>分页结果</returns>
        public List<T> SplitPage<T>(IQueryable<T> queryable, Pages pages)
        {
            // 如果分页信息为空，返回全部数据
            if (pages == null)
            {
                var allItems = queryable.ToList();
                allItems.ForEach(item => { if (item is IModel) { (item as IModel).SetIsLoaded(); } });
                return allItems;
            }

            // 设置总记录数
            pages.RecordSum = queryable.Count();

            //如果页码不在合理范围内，重新设置
            if (pages.PageNumber > pages.PageSum)
            {
                pages.PageNumber = pages.PageSum;
            }
            if (pages.PageNumber < 1)
            {
                pages.PageNumber = 1;
            }

            // 排序
            if (pages.SortFields.Count != 0)
            {
                foreach (KeyValuePair<string, bool> sortField in pages.SortFields)
                {
                    queryable = OrderBy<T>(queryable, sortField);
                }
            }
            else
            {
                // 没有排序规则，默认用第一个属性进行排序
                queryable = OrderBy<T>(queryable, new KeyValuePair<string, bool>(GetDefaultOrderPropertyName<T>(), true));
            }

            // 返回当页的数据
            var result = queryable
                .Skip(pages.First - 1)
                .Take(pages.RecordPaginal).ToList();
            result.ForEach(item => { if (item is IModel) { (item as IModel).SetIsLoaded(); } });
            return result;
        }



        /// <summary>
        /// 取得默认排序属性
        /// </summary>
        /// <typeparam name="T">查询对象类型</typeparam>
        /// <returns>默认排序属性名字</returns>
        private string GetDefaultOrderPropertyName<T>()
        {
            var properties = typeof(T).GetProperties().Where(p => p.GetCustomAttributes(typeof(ExtAttribute), false).Count() == 0);
            return properties.First().Name;
        }
    }
}
